﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind.NET.Model;
using Northwind.NET.Settings;
using System.ComponentModel;

namespace Northwind.NET.BLL
{
    [System.ComponentModel.DataObject()]
    public class CustomersStatistics
    {
        public class QuaterlyOrdersResultRow
        {
            [Browsable(false)]
            public int CustomerId {get; internal set;}
            public string Name { get; internal set; }
            [Browsable(false)]
            public string ContactName { get; internal set; }
            [Browsable(false)]
            public int ProductId {get; internal set; }
            public string ProductName { get; internal set; }
            // Casting to Decimal is not supported in LINQ to Entities queries, 
            // because the required precision and scale information cannot be inferred.
            public float? Q1Total { get; internal set; }
            public float? Q2Total { get; internal set; }
            public float? Q3Total { get; internal set; }
            public float? Q4Total { get; internal set; }
            public float? TotalCost { get; internal set; }
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static List<QuaterlyOrdersResultRow> QuarterlyOrders(int? customerId)
        //public static IQueryable<QuaterlyOrdersResultRow> QuarterlyOrders(int? customerId)
        {
            //
            // Should be Equivalent SQL but they differ - LINQ doesn't Group By as explected...
            //
            //select
            //rr.CustomerId,
            //rr.Name,
            //rr.ContactName,  
            //rr.ProductId,
            //rr.ProductName,
            //SUM(Q1SUM) as Q1Totals,
            //SUM(Q2SUM) as Q2Totals,
            //SUM(Q3SUM) as Q3Totals,
            //SUM(Q4SUM) as Q4Totals
            //from
            //(
            //select
            //r.CustomerId,
            //r.Name,
            //r.ContactName, 
            //r.ProductId,
            //r.ProductName,
            //r.QuarterNum,
            //SUM(r.ProductCost) as ProductCost,
            //'Q1SUM' = CASE
            //   WHEN r.QuarterNum = 1 then SUM(r.ProductCost) else 0 END,
            //'Q2SUM' = CASE
            //   WHEN r.QuarterNum = 2 then SUM(r.ProductCost) else 0 END,
            //'Q3SUM' = CASE
            //   WHEN r.QuarterNum = 3 then SUM(r.ProductCost) else 0 END,
            //'Q4SUM' = CASE
            //   WHEN r.QuarterNum = 4 then SUM(r.ProductCost) else 0 END
            //from
            //(
            //select 
            //c.ID as CustomerId, c.ContactName, c.Name, c.City, C.Country,
            //YEAR(o.OrderDate)as [YearNum], 
            //'QuarterNum' = CASE 
            //  when MONTH(ShippedDate) between 1 and 3 then 1
            //  when MONTH(ShippedDate) between 4 and 6 then 2
            //  when MONTH(ShippedDate) between 7 and 9 then 3
            //  when MONTH(ShippedDate) between 10 and 12 then 4
            //END,
            //p.ID as ProductId, p.Name as ProductName,
            //o.OrderDate,
            //d.Quantity * d.UnitPrice* (1-d.Discount)+ o.Freight as ProductCost
            //from
            //Customer c, [Order] o, OrderDetail d, Product p
            //where
            //c.ID = o.CustomerId and
            //o.Id = d.OrderId and
            //d.ProductId = p.Id

            //) r
            //group by CustomerId, [Name], ContactName, ProductId, ProductName, QuarterNum
            //) rr
            //group by CustomerId, [Name], ContactName, ProductId, ProductName
            //order by [Name] Asc, ProductName Asc

            NorthwindNETEntities _context = Globals.CreateObjectContext();

            var result1 = from c in _context.Customer
                          join o in _context.Order
                          on c.ID equals o.CustomerId
                          join d in _context.OrderDetail
                          on o.ID equals d.OrderID
                          join p in _context.Product
                          on d.ProductID equals p.ID
                          where (c.ID == customerId || customerId == null)
                          select new
                          {
                              CustomerId = c.ID,
                              c.ContactName,
                              c.Name,
                              c.City,
                              c.Country,
                              YearNum = (o.OrderDate.HasValue) ? ((DateTime)o.ShippedDate).Year : -1,
                              MonthNum = (o.OrderDate.HasValue) ? ((DateTime)o.ShippedDate).Month : -1,
                              ProductId = p.ID,
                              ProductName = p.Name,
                              o.ShippedDate,
                              ProductCost = d.Quantity * (float?)d.UnitPrice * (1 - d.Discount)
                          };
            var result2 = from r in result1
                          select new
                          {
                              r.CustomerId,
                              r.ContactName,
                              r.Name,
                              r.City,
                              r.Country,
                              r.YearNum,
                              r.MonthNum,
                              QuarterNum = (r.MonthNum >= 1 && r.MonthNum <= 3) ? "1" :
                                           (r.MonthNum >= 4 && r.MonthNum <= 6) ? "2" :
                                           (r.MonthNum >= 7 && r.MonthNum <= 9) ? "3" :
                                           (r.MonthNum >= 10 && r.MonthNum <= 12) ? "4" : "-1",
                              r.ProductId,
                              r.ProductName,
                              r.ShippedDate,
                              r.ProductCost
                          };

            var result3 = from r in result2
                          group r by r.ContactName + "-" + r.ProductName + "-" + r.QuarterNum into g
                          select new
                          {
                              GroupKey = g.Key,
                              CustomerId = g.Min(r => r.CustomerId),
                              Name = g.Min(r => r.Name),
                              ContactName = g.Min(r => r.ContactName),
                              ProductId = g.Min(r => r.ProductId),
                              ProductName = g.Min(r => r.ProductName),
                              QuarterNum = g.Min(r => r.QuarterNum),
                              TotalCost = g.Sum(r => r.ProductCost)
                          };

            var result4 = from r in result3
                          select new
                          {
                              r.GroupKey,
                              r.CustomerId,
                              r.Name,
                              r.ContactName,
                              r.ProductId,
                              r.ProductName,
                              r.QuarterNum,
                              r.TotalCost,
                              Q1Sum = (r.QuarterNum == "1") ? r.TotalCost : 0,
                              Q2Sum = (r.QuarterNum == "2") ? r.TotalCost : 0,
                              Q3Sum = (r.QuarterNum == "3") ? r.TotalCost : 0,
                              Q4Sum = (r.QuarterNum == "4") ? r.TotalCost : 0,

                          };

            var result5 = from r in result4
                          group r by r.GroupKey into g
                          select new
                          {
                              GroupKey = g.Key,
                              CustomerId = g.Min(r => r.CustomerId),
                              Name = g.Min(r => r.Name),
                              ContactName = g.Min(r => r.ContactName),
                              ProductId = g.Min(r => r.ProductId),
                              ProductName = g.Min(r => r.ProductName),
                              Q1Total = g.Sum(r => r.Q1Sum),
                              Q2Total = g.Sum(r => r.Q2Sum),
                              Q3Total = g.Sum(r => r.Q3Sum),
                              Q4Total = g.Sum(r => r.Q4Sum),
                              TotalCost = g.Sum(r => r.Q1Sum + r.Q2Sum + r.Q3Sum + r.Q4Sum)
                          };

            var result6 = from r in result5
                          orderby r.CustomerId ascending, r.Name ascending, r.ProductName ascending
                          select new QuaterlyOrdersResultRow
                          {
                               CustomerId = r.CustomerId,
                               Name = r.Name,
                               ContactName = r.ContactName,
                               ProductId = r.ProductId,
                               ProductName = r.ProductName,
                               Q1Total = r.Q1Total ,
                               Q2Total = r.Q2Total,
                               Q3Total = r.Q3Total,
                               Q4Total = r.Q4Total,
                               TotalCost = r.TotalCost 
                          };

            return result6.ToList <QuaterlyOrdersResultRow>(); 
        }
    }
}
